''' 员工管理 '''
import sys,traceback,logging,datetime,re
import pymysql
from dbutils.pooled_db import PooledDB 
from utils.db_config import mysqlInfo
import math
import openpyxl
import base64
from openpyxl.drawing.image import Image
from openpyxl_image_loader import SheetImageLoader
from utils.process_image import compressImgToBase64
from utils.process_image import process_image

import io

logger = logging.getLogger('app')

# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=mysqlInfo['host'],port=mysqlInfo['port'],
        user=mysqlInfo['user'],password=mysqlInfo['password'],database=mysqlInfo['database'],charset=mysqlInfo['charset'],cursorclass=pymysql.cursors.DictCursor)
    return pool.connection()

'''
用户档案页面（组） start
'''
'''用户列表 start  Finished by cdx'''
#获取user信息数据
def get_employee_data(param):
    conn = connect_db()
    cur = conn.cursor()
    pageSize = param['pageSize']
    pageCurrent = param['pageCurrent']
    searchValue = param['searchValue']
    selectProjectId = param['selectProjectId']
    selectGroupId = param['selectGroupId']
    result = {}
    emploee_list = []
    try:
        #构建长字符串常用的方式，这不是元组！
        base_sql = ("select e.id AS userId,e.gender AS gender,e.name AS userName,p.name AS projectName,g.group_name AS groupName, e.photo AS photo "
                    "from employee e left outer join projects p on e.project_id = p.id left outer join project_groups g on e.group_id = g.group_id "
        )
        # 筛选条件
        sql_conditions = []
        sql_params = []
        # 动态添加sql命令
        if searchValue:
            sql_conditions.append("e.name LIKE %s")
            sql_params.append(f"%{searchValue}%") # 进行模糊匹配，%是LIKE子句的一部分
        if selectProjectId:
            sql_conditions.append("p.id = %s")
            sql_params.append(selectProjectId)
        if selectGroupId:
            sql_conditions.append("g.group_id = %s")
            sql_params.append(selectGroupId)
        # 完整的SQL查询
        if sql_conditions:
            sql = f"{base_sql} WHERE {' AND '.join(sql_conditions)}" #' AND '.join(conditions) .join是python中的字符串方法，将可迭代对象中的所有元素连接成一个单一的字符串，调用其自身的字符串作为分隔符
            cur.execute(sql, sql_params)
        else:
            cur.execute(base_sql)

        fetchall = cur.fetchall()
        itemCount = len(fetchall)
        pageCount = math.ceil(itemCount / pageSize)
        start_idx = (pageCurrent - 1) * pageSize
        end_idx = start_idx + pageSize
        pageData = fetchall[start_idx:min(end_idx, itemCount)]
        for row in pageData:
            row_dict = {}
            row_dict['userId'] = row['userId']
            row_dict['userName'] = row['userName']
            row_dict['projectName'] = row['projectName']
            row_dict['groupName'] = row['groupName']
            row_dict['avatar'] = row['photo']
            row_dict['gender'] = row['gender']
            emploee_list.append(row_dict)

        resultData = {
            'page': pageCurrent,
            'pageSize': pageSize,
            'pageCount': pageCount,
            'itemCount': itemCount,
            'list': emploee_list
        }
        result['code'] = 200
        result['result'] = resultData
        result['message'] = 'ok'
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        # print(f"result of get_emploee_data: {result}")
        cur.close()
        conn.close()
        return result

#获取选择项目下拉框选项
def get_projectOptions():
    conn = connect_db()
    cur = conn.cursor()
    projcetOptions = []
    result = {}
    try:
        sql = "SELECT name, id FROM projects ORDER BY id"
        cur.execute(sql)
        for row in cur.fetchall():
            projcetOption_dict = {}
            projcetOption_dict["label"] = row['name']
            projcetOption_dict["value"] = str(row['id']) #ptions的value标签要求是字符串类型，数字可不行
            projcetOptions.append(projcetOption_dict)
        result['result'] = projcetOptions
        result['code'] = 200
        result['message'] = 'ok'
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        # print(f"projcetOptions:{len(projcetOptions)}")
        return result
#get_projectOptions()

#获取选择班组下拉框选项
def get_groupOptions(selectProjectId):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    groupOptions = []
    try:
        sql = ("SELECT g.group_name AS groupName, g.group_id AS groupId FROM groups_in_project gp JOIN projects p ON gp.project_id = p.id JOIN project_groups g ON gp.group_id = g.group_id WHERE p.id = %s")
        cur.execute(sql, [selectProjectId])
        for row in cur.fetchall():
            projcetOption_dict = {}
            projcetOption_dict["label"] = row['groupName']
            projcetOption_dict["value"] = str(row['groupId'])
            groupOptions.append(projcetOption_dict)
        logger.info(groupOptions)
        result['result'] = groupOptions
        result['code'] = 200
        result['message'] = 'ok'
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        # print(groupOptions)
        return result
# get_groupOptions(1)

#添加employee信息数据
def add_employee(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'username': 'name',
        'idCard': 'idCard',
        'project': 'project_id',
        'group': 'group_id',
        'role': 'role',
        'photo': 'photo',
        'gender': 'gender',
    }
    idCard = params.get('idCard')
    project_id = params.get('project')
    result = {}
    # 将彩色照片处理为符合人脸识别器要求的数据，尚未做报错处理
    params['photo'] = process_image(params['photo'])
    try:
        sql = ("SELECT * FROM employee WHERE employee.idCard = %s AND employee.project_id = %s")
        cur.execute(sql, [idCard, project_id])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = f'身份证号为{idCard}的用户已经在项目中'
            return result

        insert_fields = []
        placeholders = []
        insert_values = []
        for key, value in params.items():
            if value is not None and key != 'employee_id':
                insert_fields.append(field_mapping[key])
                placeholders.append('%s')
                insert_values.append(value)
        sql = f"INSERT INTO employee({', '.join(insert_fields)}) VALUES({', '.join(placeholders)})"
        cur.execute(sql, insert_values)

        result['code'] = 200
        result['message'] = '新增用户成功'
        result['result'] = 'ok'
        conn.commit()
    except :
        result['code'] = 0
        result['message'] = '新增失败，请联系管理员'
        result['result'] = 'ng'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#employee编辑页面初始化数据
def edit_employee_init(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    id = params['employeeID']
    result = {}
    try:
        sql = (f"SELECT e.*, p.name AS project_name, g.group_name "
               f"FROM employee e left outer join projects p on e.project_id = p.id left outer join project_groups g on e.group_id = g.group_id "
               f"WHERE e.id = %s")
        cur.execute(sql,[id])
        row = cur.fetchone()
        result['result'] = row
        result['code'] = 200
        result['message'] = 'ok'
        conn.commit()
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '编辑页面初始化失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        # print(result)
        cur.close()
        conn.close()
        return result

#更新emploee信息数据
def update_employee(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'username': 'name',
        'idCard': 'idCard',
        'project': 'project_id',
        'group': 'group_id',
        'role': 'role',
        'photo': 'photo',
        'gender': 'gender',
        'employee_id': 'id'
    }
    result = {}
    try:
        update_fields = []
        update_values = []
        for key, value in params.items():
            if value is not None:
                update_fields.append(f"{field_mapping[key]} = %s")
                update_values.append(value)
        sql = f"UPDATE employee SET {', '.join(update_fields)} WHERE employee.id = %s"
        update_values.append(params['employee_id'])
        # print(sql, update_values)
        cur.execute(sql,update_values)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = 'ok'
        conn.commit()
    except :
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '更新信息失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#删除employee信息数据（项目中的）
def del_project_employee(params: dict[str: list]):
    conn = connect_db()
    cur = conn.cursor()
    employeeId_list = params.get('delUserList', '')
    result = {}
    try:
        placeholders = ', '.join(['%s']*len(employeeId_list))
        sql = (f"DELETE FROM employee WHERE id in ({placeholders})")
        cur.execute(sql, employeeId_list)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = '成功删除'

        conn.commit()  # 提交事务
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '删除失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#批量导入人员信息数据
def import_employee(ws):
    print(123)
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        i = 2
        image_loader = SheetImageLoader(ws)
        while True:
            if ws['A' + str(i)].value is not None:
                data = {}
                data['name'] = ws['A' + str(i)].value
                data['gender'] = ws['B' + str(i)].value
                image = image_loader.get('C' + str(i))
                # 使用BytesIO来创建一个二进制数据的缓冲区
                buffer = io.BytesIO()
                # 将JpegImageFile对象保存到缓冲区中
                image.save(buffer, format="JPEG")
                # 获取包含图像的二进制数据
                binary_data = buffer.getvalue()
                # 将图片数据转换为base64编码
                base64_data = "data:image/jpeg;base64," + compressImgToBase64(buffer)
                data['photo'] = base64_data
                # 使用完毕后关闭缓冲区
                buffer.close()
                data['idCard'] = ws['D' + str(i)].value
                if ws['E' + str(i)].value is not None:
                    cur.execute("select id from projects where name like '%" + ws['E' + str(i)].value + "%'")
                    for row in cur.fetchall():
                        data['project_id'] = row['id']
                else:
                    data['project_id'] = ''
                if ws['F' + str(i)].value is not None:
                    cur.execute("select group_id from project_groups where group_name like '%" + ws['F' + str(i)].value + "%'")
                    for row in cur.fetchall():
                        data['group_id'] = row['group_id']
                else:
                    data['group_id'] = ''
                if ws['G' + str(i)].value == '管理人员':
                    data['role'] = '1'
                else:
                    data['role'] = '0'
                sql = "insert into employee(name,gender,photo,idCard,project_id,group_id,role) values(%s,%s,%s,%s,%s,%s,%s) "
                cur.execute(sql, (data['name'],data['gender'],data['photo'],data['idCard'],data['project_id'],data['group_id'],data['role']))
            else:
                break;
            i += 1
            
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = ''

        conn.commit()  # 提交事务
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '导入失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#批量导出人员信息数据
def getEmpExcel(dirpath, param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        # 打开导出数据模板
        wb = openpyxl.load_workbook(dirpath + '/人员信息.xlsx')
        ws = wb['人员信息']
        i = 2
        # 获取人员数据
        sql = "select e.name,e.gender,e.photo,e.idCard,p.name as projectname,g.group_name as groupname, case when e.role = '1' then '管理人员' else '工人' end as rolename " \
              + " from employee e left outer join projects p on e.project_id = p.id left outer join project_groups g on e.group_id = g.group_id  where 1=1 "
        if 'selectProject' in param.keys():
            sql += " and e.project_id = " + param['selectProject']
        if 'selectGroup' in param.keys():
            sql += " and e.group_id = " + param['selectGroup']
        cur.execute(sql)
        for row in cur.fetchall():
            ws['A' + str(i)] = row['name']
            ws['B' + str(i)] = row['gender']
            if row['photo'] is not None:
                # 解码base64字符串为二进制数据
                photodata = row['photo'][re.search('base64,',row['photo']).end():]
                img_data = base64.b64decode(photodata)
                # 将图片数据转换为文件对象
                img_file = io.BytesIO(img_data)
                # 将图片插入到Excel的A1单元格
                img = Image(img_file)
                img.width, img.height = 100, 150 
                ws.add_image(img, 'C' + str(i))
                ws.row_dimensions[i].height = 120
            else:
                 ws['C' + str(i)] = ''
            ws['D' + str(i)] = row['idCard']
            ws['E' + str(i)] = row['projectname']
            ws['F' + str(i)] = row['groupname']
            ws['G' + str(i)] = row['rolename']
            i += 1


        wb.save(dirpath + '/export人员信息.xlsx')

        result['result'] = ''
        result['code'] = 200
        result['message'] = ''

    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '导出失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result
'''用户列表 end  Finished by cdx'''
'''
用户档案页面（组） end
'''
